# -*- coding:utf-8 -*-
#!/usr/bin/python3


import time
import smtplib
from email.mime.text import MIMEText
from email.header import Header


import time
import datetime
import mysql.connector

# 创建链接数据库
config1 = {'host': '192.168.1.101',  # 默认127.0.0.1
          'user': 'root', 'password': 'Ccii@562>c', 'port': 3306,  # 默认即为3306
          'database': 'ccii_redesign', 'charset': 'utf8'  # 默认即为utf8
          }

config = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }



'''
查询所有数据汇总到今天
'''


'''
查询所有用户数据
AND is_enable = 1
AND del_flag = 0
AND role_type = 2
AND dtype = "Member"
'''
def findUserList(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        data = ()

        stmt = 'select su.id userId, ss.id storeId, su.company_id from shop_user su ' \
               'left join shop_store ss on su.id = ss.business_id ' \
               'where su.del_flag = 0 and su.is_enabled = 1 and  su.is_locked = 0 ' \
               "and su.role_type = 2 AND su.dtype = 'Member'  "

        # print(date.strftime('%Y-%m-%d'))
        cursor.execute(stmt)
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
查询店铺下商品
返回商品id集合
'''
def findStorePorductList(storeId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from shop_product where store_id = %s '
        cursor.execute(stmt, params=(storeId, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询店铺访客数
'''
def findStoreUserCount(type, dataId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select count(*) from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id = %s group by data_id '
        cursor.execute(stmt, params=(type, date, dataId ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
查询店铺浏览量
'''
def findStoreVisitCount(type, dataId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id = %s'
        cursor.execute(stmt, params=(type, date, dataId ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
查询商品浏览量
'''
def findProductVisitCount(type, date, productIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id in (%s)'
        cursor.execute(stmt, params=(type, date, productIds ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询商品浏览量
'''
def findProductVisitCount(type, date, productIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id in (%s)'
        cursor.execute(stmt, params=(type, date, productIds ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询商品访客数
'''
def findProductUserCount(type, date, productIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id in (%s) group by user_id '
        cursor.execute(stmt, params=(type, date, productIds ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询被访问商品数
'''
def findProductViewedCount(type, date, productIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select id from data_visit_log where type = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s ' \
               ' and data_id in (%s) group by data_id '
        cursor.execute(stmt, params=(type, date, productIds ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询商品收藏数
'''
def findProductFavoriteCount(date, productIds):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select count(*) from shop_product_favorite where product_id in (%s) and DATE_FORMAT(created_date,"%Y-%m-%d") = %s '
        cursor.execute(stmt, params=(productIds, date ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
查询店铺收藏数
'''
def findStoreFavoriteCount(date, storeId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select count(*) from shop_store_favorite where store_id = %s and DATE_FORMAT(created_date,"%Y-%m-%d") = %s '
        cursor.execute(stmt, params=(storeId, date ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询店铺收藏数
'''
def findDemandCount(date, userId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = ' select sum(hit_times) from shop_supply_demand where type = 1 and user_id = %s and DATE_FORMAT(create_date,"%Y-%m-%d") = %s '
        cursor.execute(stmt, params=(userId, date ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
保存邮件数据内容到数据库
'''

def saveData(inData):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into data_member_count(user_id, data_date, create_date, store_user_count, store_visit_count, ' \
               'product_visit_count, product_user_count, product_viewed_count, product_favorite_count, ' \
               'store_favorite_count, demand_count, del_flag) values (%s, %s, NOW(), %s, %s, %s, %s, %s, %s, %s, %s, 0)'
        cursor.executemany(stmt, inData)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('插入数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接



# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset)\
        # .strftime('%Y-%m-%d')
    # print(re_date)
    return re_date


'''
主调用方法
'''
def handleData(date, userItem):
    record = []
    # 0 为userId 1 为storeId 2 为companyId
    item = userItem
    storeUserCount = 0
    storeUserCountData = findStoreUserCount(11, item[1], date)
    if not len(storeUserCountData) == 0:
        storeUserCount = storeUserCountData[0][0]
    print("storeUserCount=" + str(storeUserCount))

    productVisitCount = 0
    productTuple = findStorePorductList(item[1])
    productList = []
    productIds = ''
    if not len(productTuple) == 0:
       for product in productTuple:
           productList.append(str(product[0]))

       productIds = ",".join(productList)

    if not productIds == '':
        productVisitCount = len(findProductVisitCount(10, date, productIds))

    print("productVisitCount=" + str(productVisitCount))

    # 店铺浏览量
    storeVisitCount = 0
    storeVisitCountList = findStoreVisitCount(11, item[1], date)
    if len(storeVisitCountList) == 0:
        print("storeVisitCount=" + str(storeVisitCount + productVisitCount))
    else:
        print("storeVisitCount=" + str(len(storeVisitCountList) + productVisitCount))

    # 商品访客数
    productUserCount = 0
    if not productIds == '':
        print(productIds)
        productUserCountData = findProductUserCount(10, date, productIds)
        if not len(productUserCountData) == 0:
            productUserCount = len(productUserCountData)
    print("productUserCount=" + str(productUserCount))

    # 被访问商品数
    productViewedCount = 0
    if not productIds == '':
        productViewedCountData = findProductVisitCount(10, date, productIds)
        if not len(productViewedCountData) == 0:
            productViewedCount = len(productViewedCountData)
    print("productViewedCount=" + str(productViewedCount))

    # 商品收藏数
    productFavoriteCount = 0
    if not productIds == '':
        productFavoriteCountData = findProductFavoriteCount(date, productIds)
        if not len(productFavoriteCountData) == 0:
            productFavoriteCount = productFavoriteCountData[0][0]
    print("productFavoriteCount=" + str(productFavoriteCount))

    # 店铺收藏数
    storeFavoriteCount = 0
    storeFavoriteCountData = findStoreFavoriteCount(date, item[1])
    if not len(storeFavoriteCountData) == 0:
        storeFavoriteCount = storeFavoriteCountData[0][0]
    print("storeFavoriteCount=" + str(storeFavoriteCount))

    demandCount = 0
    demandCountData = findDemandCount(date, item[0])
    if not len(demandCountData) == 0 :
        if  demandCountData[0][0] :
            demandCount = demandCountData[0][0]

    print("demandCount=" + str(demandCount))

    record.append(item[0])
    record.append(date)
    record.append(storeUserCount)
    record.append(storeVisitCount)
    record.append(productVisitCount)
    record.append(productUserCount)
    record.append(productViewedCount)
    record.append(productFavoriteCount)
    record.append(storeFavoriteCount)
    record.append(demandCount)
    return record


    # try:
    #     saveEmailContent(content)
    # except mysql.connector.Error as e:
    #     print('保存邮件数据异常', str(e))


def getRecordByUserId(userId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'select store_user_count, store_visit_count, ' \
               'product_visit_count, product_user_count, product_viewed_count, product_favorite_count, ' \
               'store_favorite_count, demand_count from data_member_count where user_id = %s order by id desc limit 1'
        cursor.execute(stmt, params = (str(userId),))
        data = cursor.fetchall()

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
昨天的累计数据加今天的数据
'''
def plusYesterday(record, userId):
    oldRecord = getRecordByUserId(userId)
    if oldRecord:
        record[2] = record[2] + oldRecord[0][0]
        record[3] = record[3] + oldRecord[0][1]
        record[4] = record[4] + oldRecord[0][2]
        record[5] = record[5] + oldRecord[0][3]
        record[6] = record[6] + oldRecord[0][4]
        record[7] = record[7] + oldRecord[0][5]
        record[8] = record[8] + oldRecord[0][6]

    print(oldRecord)
    print(record)
    return record


def work():
    yesterday = getdate(1)
    date = yesterday.strftime('%Y-%m-%d')
    userData = findUserList(date)
    print(len(userData))
    insertData = []
    for i, userItem in enumerate(userData):
        # if i < 2:
        newRecord = handleData(date, userItem)
        userId = newRecord[0]
        insertData.append(plusYesterday(newRecord, userId))

    if len(insertData) > 0:
        saveData(insertData)



# 程序运行主方法
work()


